'use strict';

const Service = require('egg').Service;
const moment = require('moment');

/**
 * 投稿数据服务类
 * 处理投稿数据相关的业务逻辑
 */
class ContributionDataService extends Service {
  /**
   * 获取待补充数据的投稿列表
   * 返回已审核但缺少平台数据的投稿
   * @returns {Promise<Array>} 投稿列表
   */
  async getPendingDataList() {
    const sql = `
      SELECT 
        c.*,
        p1.person_name as author1_name,
        p2.person_name as author2_name,
        p3.person_name as author3_name,
        d1.department_name as author1_department_name,
        d2.department_name as author2_department_name,
        d3.department_name as author3_department_name,
        pg.project_group_name
      FROM 
        tb_contribution c
        LEFT JOIN tb_person p1 ON c.author1 = p1.person_id
        LEFT JOIN tb_person p2 ON c.author2 = p2.person_id
        LEFT JOIN tb_person p3 ON c.author3 = p3.person_id
        LEFT JOIN tb_department d1 ON c.author1_department_id = d1.department_id
        LEFT JOIN tb_department d2 ON c.author2_department_id = d2.department_id
        LEFT JOIN tb_department d3 ON c.author3_department_id = d3.department_id
        LEFT JOIN tb_project_group pg ON c.project_group_id = pg.project_group_id
      WHERE 
        c.isDeleted = 0 
        AND c.approved_status = 1
        AND (
          c.tiktok_likes IS NULL 
          OR c.channels_views IS NULL 
          OR c.channels_likes IS NULL
        )
      ORDER BY 
        c.submission_date DESC
    `;

    return await this.app.mysql.query(sql);
  }

  /**
   * 导入平台数据
   * @param {Object} data - 平台数据
   * @param {number} data.contribution_id - 投稿ID
   * @param {number} data.tiktok_likes - 抖音点赞数
   * @param {number} data.channels_views - 视频号播放量
   * @param {number} data.channels_likes - 视频号点赞数
   * @param {string} data.platform_submit_date - 平台提交日期（格式：YYYY/MM/DD）
   * @returns {Promise<Object|{error: string}|null>} 更新后的投稿信息，不存在返回null，未审核返回错误信息
   */
  async importPlatformData(data) {
    const {
      contribution_id,
      tiktok_likes,
      channels_views,
      channels_likes,
      platform_submit_date // 新增入参：YYYY/MM/DD格式
    } = data;

    // 验证日期格式并转换为数据库timestamp格式（YYYY-MM-DD HH:mm:ss）
    const dateFormat = 'YYYY/MM/DD';
    if (!moment(platform_submit_date, dateFormat, true).isValid()) {
      return {
        error: 'invalid_date_format'
      }; // 格式无效（严格模式验证）
    }

    // 转换为数据库兼容的timestamp格式（补全时分秒为00:00:00）
    const formattedDate = moment(platform_submit_date, dateFormat).format('YYYY-MM-DD HH:mm:ss');

    // 先检查投稿是否存在且未删除
    const contribution = await this.app.mysql.get('tb_contribution', {
      contribution_id,
      isDeleted: 0
    });

    if (!contribution) {
      return null;
    }

    // 检查是否已审核通过
    if (contribution.approved_status !== 1) {
      return {
        error: 'not_approved'
      };
    }

    // 更新平台数据及提交日期
    await this.app.mysql.update('tb_contribution', {
      tiktok_likes,
      channels_views,
      channels_likes,
      platform_submit_date: formattedDate // 存入转换后的timestamp格式
    }, {
      where: {
        contribution_id
      }
    });

    // 返回更新后的完整信息
    const sql = `
    SELECT 
      c.*,
      p1.person_name as author1_name,
      p2.person_name as author2_name,
      p3.person_name as author3_name,
      d1.department_name as author1_department_name,
      d2.department_name as author2_department_name,
      d3.department_name as author3_department_name,
      pg.project_group_name
    FROM 
      tb_contribution c
      LEFT JOIN tb_person p1 ON c.author1 = p1.person_id
      LEFT JOIN tb_person p2 ON c.author2 = p2.person_id
      LEFT JOIN tb_person p3 ON c.author3 = p3.person_id
      LEFT JOIN tb_department d1 ON c.author1_department_id = d1.department_id
      LEFT JOIN tb_department d2 ON c.author2_department_id = d2.department_id
      LEFT JOIN tb_department d3 ON c.author3_department_id = d3.department_id
      LEFT JOIN tb_project_group pg ON c.project_group_id = pg.project_group_id
    WHERE 
      c.contribution_id = ?
  `;

    const result = await this.app.mysql.query(sql, [contribution_id]);
    return result[0];
  }

  /**
   * 获取投稿统计列表
   * @param {Object} params - 查询参数
   * @param {string} [params.start_date] - 开始日期
   * @param {string} [params.end_date] - 结束日期
   * @param {number} [params.project_group_id] - 项目组ID
   * @param {number} [params.department_id] - 部门ID
   * @param {string} [params.person_name] - 人员姓名
   * @param {number} [params.approved_status] - 审核状态
   * @param {0|1|2} [params.has_platform_submit_date] - 平台提交日期筛选（0:所有, 1:有值, 2:无值）
   * @returns {Promise<Array>} 投稿列表
   */
  async getStatsList(params) {
    const {
      start_date,
      end_date,
      project_group_id,
      department_id,
      person_name,
      approved_status,
      has_platform_submit_date // 新增：0=所有，1=有值，2=无值
    } = params;

    // 构建 WHERE 条件
    let conditions = ['c.isDeleted = 0'];
    const queryParams = [];

    // 添加时间范围条件（原创建时间）
    if (start_date) {
      conditions.push('c.created_at >= ?');
      queryParams.push(`${start_date} 00:00:00`);
    }

    if (end_date) {
      conditions.push('c.created_at <= ?');
      queryParams.push(`${end_date} 23:59:59`);
    }

    // 修正：平台提交日期筛选（针对 TIMESTAMP 类型调整判断逻辑）
    if (has_platform_submit_date === 1) {
      // 1: 筛选有值的记录（TIMESTAMP 类型有值即不为 NULL）
      conditions.push('c.platform_submit_date IS NOT NULL');
    } else if (has_platform_submit_date === 2) {
      // 2: 筛选无值的记录（TIMESTAMP 类型无值即 NULL）
      conditions.push('c.platform_submit_date IS NULL');
    }

    // 添加项目组条件
    if (project_group_id) {
      conditions.push('c.project_group_id = ?');
      queryParams.push(project_group_id);
    }

    // 添加部门条件
    if (department_id) {
      conditions.push(`(
      c.author1_department_id = ? OR 
      c.author2_department_id = ? OR 
      c.author3_department_id = ?
    )`);
      queryParams.push(department_id, department_id, department_id);
    }

    // 添加人员姓名条件
    if (person_name) {
      conditions.push(`(
      p1.person_name LIKE ? OR 
      p2.person_name LIKE ? OR 
      p3.person_name LIKE ?
    )`);
      queryParams.push(`%${person_name}%`, `%${person_name}%`, `%${person_name}%`);
    }

    // 添加审核状态条件
    if (approved_status !== undefined) {
      conditions.push('c.approved_status = ?');
      queryParams.push(approved_status);
    }

    // 查询数据
    const sql = `
    SELECT c.*, 
      p1.person_name as author1_name,
      p2.person_name as author2_name,
      p3.person_name as author3_name,
      d1.department_name as author1_department_name,
      d2.department_name as author2_department_name,
      d3.department_name as author3_department_name,
      pg.project_group_name,
      sp.person_name as scoring_person_name
    FROM tb_contribution c
    LEFT JOIN tb_person p1 ON c.author1 = p1.person_id
    LEFT JOIN tb_person p2 ON c.author2 = p2.person_id
    LEFT JOIN tb_person p3 ON c.author3 = p3.person_id
    LEFT JOIN tb_department d1 ON c.author1_department_id = d1.department_id
    LEFT JOIN tb_department d2 ON c.author2_department_id = d2.department_id
    LEFT JOIN tb_department d3 ON c.author3_department_id = d3.department_id
    LEFT JOIN tb_project_group pg ON c.project_group_id = pg.project_group_id
    LEFT JOIN tb_person sp ON c.scoring_person_id = sp.person_id
    WHERE ${conditions.join(' AND ')}
    ORDER BY c.submission_date DESC
  `;

    return await this.app.mysql.query(sql, queryParams);
  }

  /**
   * 获取投稿概览统计
   * @param {Object} params - 查询参数
   * @param {string} [params.start_date] - 开始日期
   * @param {string} [params.end_date] - 结束日期
   * @returns {Promise<Object>} 统计结果
   */
  async getOverview(params) {
    const {
      start_date,
      end_date
    } = params;

    // 构建时间条件
    let conditions = ['isDeleted = 0'];
    const queryParams = [];

    if (start_date) {
      conditions.push('created_at >= ?');
      queryParams.push(`${start_date} 00:00:00`);
    }

    if (end_date) {
      conditions.push('created_at <= ?');
      queryParams.push(`${end_date} 23:59:59`);
    }

    const whereClause = conditions.join(' AND ');

    const sql = `
      SELECT
        COUNT(*) as total_count,
        SUM(CASE WHEN approved_status = 1 THEN 1 ELSE 0 END) as approved_count,
        SUM(CASE WHEN approved_status = 0 THEN 1 ELSE 0 END) as pending_count,
        SUM(CASE WHEN approved_status = 2 THEN 1 ELSE 0 END) as rejected_count,
        SUM(CASE 
          WHEN approved_status = 1 AND score IS NULL 
          THEN 1 ELSE 0 END) as unscored_count
      FROM tb_contribution
      WHERE ${whereClause}
    `;

    const result = await this.app.mysql.query(sql, queryParams);

    return {
      total_count: result[0].total_count || 0, // 总投稿数量
      approved_count: result[0].approved_count || 0, // 已审核数量
      pending_count: result[0].pending_count || 0, // 待审核数量
      rejected_count: result[0].rejected_count || 0, // 未采用数量
      unscored_count: result[0].unscored_count || 0 // 已审核未打分数量
    };
  }
}

module.exports = ContributionDataService;